package com.sp2p.service.admin;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.shove.Convert;
import com.shove.base.BaseService;
import com.shove.data.DataException;
import com.shove.data.DataSet;
import com.shove.data.dao.MySQL;
import com.shove.util.BeanMapUtils;
import com.shove.vo.PageBean;
import com.sp2p.constants.IConstants;
import com.sp2p.dao.admin.StatisManageDao;
import com.sp2p.database.Dao.Procedures;
import com.sp2p.database.Dao.Views.intentionfund_user;

/**
 * @ClassName: AfterCreditManageService.java
 * @Author: gang.lv
 * @Date: 2013-3-19 上午10:18:35
 * @Copyright: 2013 www.emis.com Inc. All rights reserved.
 * @Version: V1.0.1
 * @Descrb: 统计管理业务处理层
 */
public class StatisManageService extends BaseService {

	public static Log log = LogFactory.getLog(StatisManageService.class);

	private StatisManageDao statisManageDao;

	public StatisManageDao getStatisManageDao() {
		return statisManageDao;
	}

	public void setStatisManageDao(StatisManageDao statisManageDao) {
		this.statisManageDao = statisManageDao;
	}

	/**
	 * @throws DataException
	 * @MethodName: queryLoginStatisByCondition
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-4 上午09:49:43
	 * @Return:
	 * @Descb: 查询登录统计
	 * @Throws:
	 */
	public void queryLoginStatisByCondition(String userName, String realName,
			String timeStart, String timeEnd, int isRealName,String zhuStart,String zhuEnd, PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " * ";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(userName)) {
			condition.append(" and username  like '%"
					+ StringEscapeUtils.escapeSql(userName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(realName)) {
			condition.append(" and realname  like '%"
					+ StringEscapeUtils.escapeSql(realName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and lastDate >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and lastDate <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (StringUtils.isNotBlank(zhuStart)) {
			condition.append(" and createTime >= '"
					+ StringEscapeUtils.escapeSql(zhuStart) + "'");
		}
		if (StringUtils.isNotBlank(zhuEnd)) {
			condition.append(" and createTime <= '"
					+ StringEscapeUtils.escapeSql(zhuEnd) + "'");
		}
		if (isRealName == 0){
			condition.append(" and (idNo='' or idNo is null) ");
		}
		if (isRealName == 1){
			condition.append(" and idNo!=''");
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_login_statis", resultFeilds, " ",
					condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	/**
	 * @throws DataException
	 * @MethodName: queryLoginStatisByCondition
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-4 上午09:49:43
	 * @Return:
	 * @Descb: 查询登录统计总计
	 * @Throws:
	 */
	public Map<String,String> queryLoginStatisByConditionSize(String userName, String realName,
			String timeStart, String timeEnd, int isRealName,
			String zhuStart,String zhuEnd
			) throws Exception {
		Map<String,String> map = new HashMap<String, String>();
		Connection conn = MySQL.getConnection();
		try {
			map=statisManageDao.queryLoginStatisByConditionSize(conn, userName, realName, timeStart, timeEnd,
					 isRealName, zhuStart, zhuEnd);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	/**
	 * @throws DataException
	 * @MethodName: queryLoginStatisByCondition
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-4 上午09:49:43
	 * @Return:
	 * @Descb: 查询登录统计
	 * @Throws:
	 */
	public void queryLoginStatisByCondition(String userName, String realName,
			String timeStart, String timeEnd, int countInt,String regStartTime,String regEndTime,String sex,String age,int minAge,int maxAge,
			PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " * ";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(userName)) {
			condition.append(" and username  like '%"
					+ StringEscapeUtils.escapeSql(userName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(realName)) {
			condition.append(" and realname  like '%"
					+ StringEscapeUtils.escapeSql(realName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and lastDate >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and lastDate <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (StringUtils.isNotBlank(regStartTime)){
			condition.append(" and createTime >= '"
					+ StringEscapeUtils.escapeSql(regStartTime) + "'");
		}
		if (StringUtils.isNotBlank(regEndTime)){
			condition.append(" and createTime <= '"
					+ StringEscapeUtils.escapeSql(regEndTime) + "'");
		}
		if (StringUtils.isNotBlank(sex)){
			condition.append(" and sex = '"
					+ StringEscapeUtils.escapeSql(sex) + "'");
		}
		if(StringUtils.isNotBlank(age)){
			if(minAge==0){
				condition.append(" and timestampdiff(YEAR, birthday, curdate()) >=" + minAge);
			}else{
				condition.append(" and timestampdiff(YEAR, birthday, curdate()) >" + minAge);
			}
			
			condition.append(" and timestampdiff(YEAR, birthday, curdate()) <" + maxAge);
		}
		if (countInt != -1) {
			if(countInt==1){//升序
				condition.append(" order by loginCount ");
			}else if(countInt==0){//降序
				condition.append(" order by loginCount desc ");
			}
			
		}
		
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_login_statis", resultFeilds, " ",
					condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	/**
	 * @throws SQLException
	 * @throws DataException
	 * @MethodName: queryInvestStatisByCondition
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-5 下午09:33:22
	 * @Return:
	 * @Descb: 查询投资统计列表
	 * @Throws:
	 */
	public void queryInvestStatisByCondition(String htNo, String investor,
			String timeStart, String timeEnd, int borrowWayInt,
			int isAutoBidInt, int borrowStatusInt, int groupInt,String investRate,int incomeWayInt,int incomeStatusInt,String incomeDay, int borrowProduct,
			PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " v.*,f_sjzd('productType',v.borrowProduct) as borrowProductRename ";
		String view = "(SELECT m.* , (SELECT CASE WHEN group_concat(b.groupName) IS NULL THEN '普通投资人' ELSE"
               + " group_concat(b.groupName) END AS groupNames FROM t_group_user a"
               + " LEFT JOIN t_group b ON a.groupId = b.id WHERE a.userId = m.userId) AS groupNames, (SELECT group_concat(a.groupId)"
               + " AS groupIds  FROM t_group_user a LEFT JOIN t_group b ON a.groupId = b.id"
               + " WHERE a.userId = m.userId) AS groupIds FROM v_t_invest_statis m) v"
               ;
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(htNo)) {
			condition.append(" and  v.agreementId  like '%"
					+ StringEscapeUtils.escapeSql(htNo.trim()) + "%' ");
		}
		if (StringUtils.isNotBlank(investor)) {
			condition.append(" and v.investor like '%"
					+ StringEscapeUtils.escapeSql(investor.trim()) + "%' " );
		}
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and v.investTime >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and v.investTime <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (borrowWayInt != -1) {
			condition.append(" and v.borrowWayYTF = " + borrowWayInt);
		}
		if (isAutoBidInt != -1) {
			condition.append(" and v.isAutoBid = " + isAutoBidInt);
		}
		if (borrowStatusInt != -1) {
			if(borrowStatusInt == 1){
				condition.append(" and v.borrowStatus not in (4,5) ");
			}else if(borrowStatusInt == 2){
				condition.append(" and v.borrowStatus in (4,5) ");
			}
		}
		if (groupInt != -1) {
			condition.append(" and groupIds like '%" + groupInt + "%'");
		}
		if (!investRate.equals("-1")){
			condition.append(" and v.annualRate = " + investRate );
		}
		if (incomeWayInt != -1){
			condition.append(" and v.paymentMode = " + incomeWayInt);
		}
		if (incomeStatusInt!=-1 && incomeStatusInt!=20 && incomeStatusInt!=21){
			condition.append(" and v.borrowStatus = " + incomeStatusInt);
		}
		if(incomeStatusInt==20){
			condition.append(" and v.borrowStatus = 4 and recivedFI = 0");
		}
		if(incomeStatusInt==21){
			condition.append(" and v.borrowStatus = 4 and recivedFI > 0");
		}
		if(!incomeDay.equals("-1")){
			condition.append(" and v.incomeDay = " + incomeDay);
		}
		if(borrowProduct != -1){
			condition.append(" and v.borrowProduct = " + borrowProduct);
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, view, resultFeilds, " ",
					condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	/**
	 * @throws SQLException
	 * @throws DataException
	 * @MethodName: queryInvestStatisByCondition
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-5 下午09:33:22
	 * @Return:
	 * @Descb: 查询投资统计列表合计
	 * @Throws:
	 */
	public Map<String,String> queryInvestStatisByConditionSize(String htNo, String investor,
			String timeStart, String timeEnd, int borrowWayInt,
			int isAutoBidInt, int borrowStatusInt, int groupInt,String investRate,int incomeWayInt,
			int incomeStatusInt,String incomeDay, int borrowProduct) throws Exception {
		Map<String,String> map = new HashMap<String, String>();
		
		Connection conn = MySQL.getConnection();
		try {
			map = statisManageDao.queryInvestStatisByConditionSize(conn, htNo, investor, timeStart, timeEnd, borrowWayInt, 
					isAutoBidInt, borrowStatusInt, groupInt, investRate, incomeWayInt, incomeStatusInt, incomeDay, borrowProduct);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();
			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	/**
	 * @throws SQLException
	 * @throws DataException
	 * @MethodName: queryInvestStatisRankByCondition
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-5 下午10:35:39
	 * @Return:
	 * @Descb: 查询投资排名列表
	 * @Throws:
	 */
	public void queryInvestStatisRankByCondition(String investor,
			String timeStart, String timeEnd, int groupInt,String order,
			PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " a.*,b.realSum ";
		StringBuffer condition = new StringBuffer();
		StringBuffer tables = new StringBuffer();
		tables
				.append(" v_t_invest_rank a left join (select investor,sum(realAmount) realSum from v_t_invest_rank where 1=1 ");
		if (StringUtils.isNotBlank(investor)) {
			condition.append(" and a.investor like '%"
					+ StringEscapeUtils.escapeSql(investor.trim()) + "%'");
			tables.append(" and investor like '%"
					+ StringEscapeUtils.escapeSql(investor.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and a.investTime >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
			tables.append(" and investTime >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and a.investTime <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
			tables.append(" and investTime <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (groupInt != -1) {
			condition.append(" and a.groupId = " + groupInt);
			tables.append(" and groupId = " + groupInt);
		}
		tables.append(" group by investor) b on a.investor = b.investor ");
		if("up".equals(order)){
			condition.append(" order by a.rating,b.realSum desc,a.realAmount desc ");
		}
		if("down".equals(order)){
			condition.append(" order by a.rating desc,b.realSum desc,a.realAmount desc ");
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, tables.toString(), resultFeilds,
					"", condition
							.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	
	/**
	 * @throws SQLException
	 * @throws DataException
	 * @MethodName: queryInvestStatisRankByCondition
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-5 下午10:35:39
	 * @Return:
	 * @Descb: 查询投资排名列表总数
	 * @Throws:
	 */
	public int queryInvestStatisRankByConditionSize(String investor,
			String timeStart, String timeEnd, int groupInt,String order) throws Exception {
		List<Map<String,String>> list = new ArrayList<Map<String,String>>();
		Connection conn = MySQL.getConnection();
		try {
			list=statisManageDao.queryInvestStatisRankSize(conn, investor, timeStart, timeEnd, groupInt, order);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return list.size();
	}

	/**
	 * @MethodName: queryBorrowStatisByCondition
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-6 上午10:23:49
	 * @Return:
	 * @Descb: 借款管理费统计
	 * @Throws:
	 */
	public void queryBorrowStatisByCondition(String htNo,
			String borrower, String timeStart, String timeEnd,
			int borrowWayInt,String repayDay,String annualRate,String borrowStatus,String repayWay,
			String mediateFeeLv, int borrowProduct,int agent, PageBean<Map<String, Object>> pageBean)
			throws Exception {
		String resultFeilds = " * ";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(htNo)) {
			condition.append(" and  agreementId  like '%"
					+ StringEscapeUtils.escapeSql(htNo.trim()) + "%' ");
		}
		if (StringUtils.isNotBlank(borrower)) {
			condition.append(" and borrower  like '%"
					+ StringEscapeUtils.escapeSql(borrower.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and auditTime >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and auditTime <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (borrowWayInt != -1) {
			condition.append(" and borrowWayYTF = " + borrowWayInt);
		}
		if(!repayDay.equals("-1")){
			condition.append(" and repayDay = " + repayDay);
		}
		if(!annualRate.equals("-1")){
			condition.append(" and annualRate = " + annualRate);
		}
		if(!borrowStatus.equals("-1")&&!borrowStatus.equals("20")&&!borrowStatus.equals("21")){
			condition.append(" and borrowStatus = " + borrowStatus);
		}
		if(borrowStatus.equals("20")){
			condition.append(" and borrowStatus = 4 and allFI = 0 ");
		}
		if(borrowStatus.equals("21")){
			condition.append(" and borrowStatus = 4 and allFI !=0 ");
		}
		if(!repayWay.equals("-1")){
			condition.append(" and paymentMode = " + repayWay);
		}
		if(!mediateFeeLv.equals("-1")){
			condition.append(" and mediateFeeLv = " + mediateFeeLv);
		}
		if(borrowProduct != -1){
			condition.append(" and borrowProduct = " + borrowProduct);
		}
		if(agent > 0){
			condition.append(" and agent = " + agent);
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_borrow_statis ", resultFeilds, " ",
					condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	
	/**
	 * @MethodName: queryBorrowStatisByCondition
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-6 上午10:23:49
	 * @Return:
	 * @Descb: 借款管理费统计合计
	 * @Throws:
	 */
	public Map<String,String> queryBorrowStatisByConditionSize(String htNo,
			String borrower, String timeStart, String timeEnd,
			int borrowWayInt,String repayDay,String annualRate,String borrowStatus,String repayWay,
			String mediateFeeLv, int borrowProduct ,int agent)throws Exception {
		Map<String,String> map = new HashMap<String, String>();
		Connection conn = MySQL.getConnection();
		try {
			map = statisManageDao.queryBorrowStatisByConditionSize(conn,htNo, borrower, timeStart, timeEnd, borrowWayInt, repayDay, annualRate, borrowStatus, repayWay, mediateFeeLv, borrowProduct,agent);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}

	/**
	 * @MethodName: queryBorrowStatisAmount
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-6 上午11:00:40
	 * @Return:
	 * @Descb: 查询借款统计总计
	 * @Throws:
	 */
	public Map<String, String> queryBorrowStatisAmount(String borrowTitle,
			String borrower, String timeStart, String timeEnd, int borrowWayInt)
			throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			map = statisManageDao.queryBorrowStatisAmount(conn, borrowTitle,
					borrower, timeStart, timeEnd, borrowWayInt);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}

	/**
	 * @MethodName: queryborrowStatisInterestByCondition
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-6 下午03:16:20
	 * @Return:
	 * @Descb: 投标借款管理费统计
	 * @Throws:
	 */
	public void queryborrowStatisInterestByCondition(String investor,
			String timeStart, String timeEnd,
			PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " a.id,a.investor,a.realName,round(b.manageFI,2) as manageFI,round(b.hasPI,2) as hasPI,round(b.manageFee,2) as manageFee, round(b.hasInterest,2) as hasInterest, round(b.forInterest,2) as forInterest,round(b.forPI,2) as forPI";
		StringBuffer condition = new StringBuffer();
		StringBuffer tables = new StringBuffer();
		tables
				.append(" v_t_invest_interest_statis a left join (select investor,sum(manageFI) manageFI,sum(hasPI) hasPI,sum(manageFee ) manageFee ,");
		tables
				.append("sum(hasInterest) hasInterest,sum(forInterest) forInterest,sum(forPI) forPI   from v_t_invest_interest_statis where 1=1 ");
		if (StringUtils.isNotBlank(investor)) {
			condition.append(" and a.investor like '%"
					+ StringEscapeUtils.escapeSql(investor.trim()) + "%'");
			tables.append(" and investor like '%"
					+ StringEscapeUtils.escapeSql(investor.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and a.investTime >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
			tables.append(" and investTime >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and a.investTime <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
			tables.append(" and investTime <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		tables.append(" group by investor) b on a.investor = b.investor");
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, tables.toString(), resultFeilds, "",
					condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	// ........................................................................................................
	/**
	 * 查询用户组统计
	 * 
	 * @param groupName
	 *            查询条件 组名
	 * @param pageBean
	 *            分页
	 * @throws SQLException
	 * @throws DataException
	 */
	public void queryborrowStatisUserGroupByCondition(String groupName,
			PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " * ";
		StringBuffer condition = new StringBuffer();
		StringBuffer tables = new StringBuffer();
		tables
				.append(" ( select ifnull(sum(a.totalSum),0) totalSum,ifnull(sum(a.usableSum),0) usableSum,ifnull(sum(a.freezeSum),0) freezeSum,round(ifnull(sum(b.forPI),0),2) forPI,round(ifnull(sum(b.forInterest),0),2) forInterest,ifnull(sum(c.manageFee),0) manageFee,");
		tables
				.append(" ifnull(sum(d.vipFee),0) vipFee,ifnull(sum(e.hasPI),0) hasPI,ifnull(sum(f.realAmount),0) realAmount,g.groupId,h.groupName from");
		tables
				.append(" v_t_group_user_amount a left join v_t_group_for_amount b on a.userId = b.userId left join v_t_group_managefee c on a.userId = c.userId left join v_t_group_vip d ");
		tables
				.append(" on a.userId = d.userId left join v_t_has_amount e on a.userId=e.userId left join  (select sum(realAmount) realAmount,userId from v_t_invest_amount group by userId) f");
		tables
				.append(" on a.userId = f.userId left join t_group_user g on a.userId = g.userId left join t_group h on g.groupId =h.id");
		tables
				.append(" where groupId is not null group by g.groupId,h.groupName) t");
		if (StringUtils.isNotBlank(groupName)) {
			condition.append(" and t.groupName ='"
					+ StringEscapeUtils.escapeSql(groupName.trim()) + "'");
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, tables.toString(), resultFeilds,
					" order by groupId desc", condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}

	// ......................................................................

	/**
	 * @MethodName: queryFinanceStatis
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-6 下午03:17:37
	 * @Return:
	 * @Descb: 查询投资统计
	 * @Throws:
	 */
	public Map<String, String> queryFinanceEarnStatis(String timeStart,
			String timeEnd) throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			DataSet ds = new DataSet();
			List<Object> outParameterValues = new ArrayList<Object>();
			Procedures.pr_getFinanceEarnStatis(conn, ds, outParameterValues,
					timeStart, timeEnd);
			ds.tables.get(0).rows.genRowsMap();
			map = BeanMapUtils.dataSetToMap(ds);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}

	/**
	 * @MethodName: queryWebStatis
	 * @Param: StatisManageService
	 * @Author: gang.lv
	 * @Date: 2013-4-6 下午03:18:16
	 * @Return:
	 * @Descb: 查询网站统计
	 * @Throws:
	 */
	public Map<String, String> queryWebStatis() throws Exception {
		Connection conn = MySQL.getConnection();
		Map<String, String> map = new HashMap<String, String>();
		try {
			DataSet ds = new DataSet();
			List<Object> outParameterValues = new ArrayList<Object>();
			Procedures.pr_getWebStatis(conn, ds, outParameterValues, -1);
			ds.tables.get(0).rows.genRowsMap();
			map = BeanMapUtils.dataSetToMap(ds);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
		return map;
	}
	
	public void changeNumToStr(PageBean<Map<String, Object>> pageBean){
		List<Map<String, Object>> list = pageBean.getPage();
		if (list != null) {
			for (Map<String, Object> map : list) {
				if (Convert.strToStr(map.get("borrowWay")+"", "").equals(
						IConstants.BORROW_TYPE_NET_VALUE)) {
					map.put("borrowWay",
							IConstants.BORROW_TYPE_NET_VALUE_STR);
				} else if (Convert.strToStr(map.get("borrowWay")+"", "").equals(
						IConstants.BORROW_TYPE_SECONDS)) {
					map
							.put("borrowWay",
									IConstants.BORROW_TYPE_SECONDS_STR);
				} else if (Convert.strToStr(map.get("borrowWay")+"", "").equals(
						IConstants.BORROW_TYPE_GENERAL)) {
					map
							.put("borrowWay",
									IConstants.BORROW_TYPE_GENERAL_STR);
				} else if (Convert.strToStr(map.get("borrowWay")+"", "").equals(
						IConstants.BORROW_TYPE_FIELD_VISIT)) {
					map.put("borrowWay",
							IConstants.BORROW_TYPE_FIELD_VISIT_STR);
				} else if (Convert.strToStr(map.get("borrowWay")+"", "").equals(
						IConstants.BORROW_TYPE_INSTITUTION_GUARANTEE)) {
					map
							.put(
									"borrowWay",
									IConstants.BORROW_TYPE_INSTITUTION_GUARANTEE_STR);
				}

				if (Convert.strToStr(map.get("isAutoBid")+"", "").equals("1")) {
					map.put("isAutoBid", "否");
				} else if (Convert.strToStr(map.get("isAutoBid")+"", "").equals("2")) {
					map.put("isAutoBid", "是");
				}

				if (Convert.strToStr(map.get("borrowStatus")+"", "").equals("4")) {
					map.put("isSuccessInvest", "是");
				} else if (Convert.strToStr(map.get("borrowStatus")+"", "").equals("5")) {
					map.put("isSuccessInvest", "是");
				} else {
					map.put("isSuccessInvest", "否");
				}

			}
		}
	}
	
	public void queryStillSumByCondition(String timeStart, String timeEnd, PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " * ";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and rq >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and rq <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " t_rb_mrzk", resultFeilds, " ",
					condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	public void queryWorkingReportByCondition(String timeStart, String timeEnd, PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " * ";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and rq >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and rq <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " t_rb_yxjb", resultFeilds, " order by rq desc",
					condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	public void queryReferrerInstoreByCondition(String timeStart, String timeEnd, String userName, int groupId, PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " * ";
		String innerCondition = "(SELECT DISTINCT userId FROM t_group_user)";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and temp.liquidationTime >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and temp.liquidationTime <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (StringUtils.isNotBlank(userName)) {
			condition.append(" and temp.username like '%"
					+ StringEscapeUtils.escapeSql(userName.trim()) + "%'");
		}
		if (groupId > 0){
			innerCondition = "(SELECT DISTINCT userId FROM t_group_user WHERE groupId = '"+groupId+"')";
		}
		String table = " (SELECT a.userId userId, date_format(a.liquidationTime, '%Y-%m-%d') liquidationTime, b.username username,round(sum(a.investAmount),2) investAmountSum, round(sum(a.awardMoney),2) awardMoneySum"
			+" FROM t_gofull_award_history a LEFT JOIN t_user b ON a.userId = b.id"
			+" WHERE a.userId IN "+innerCondition
			+" GROUP BY a.userId, a.liquidationTime) temp"
			;
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, table, resultFeilds, " ORDER BY temp.liquidationTime DESC,userId asc ", condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	/**
	 * 查询所有的用户组
	 * 
	 * @return
	 * @throws Exception
	 * @throws DataException
	 */
	public List<Map<String, Object>> queryAllGroup() throws Exception {
		Connection conn = MySQL.getConnection();

		try {
			return statisManageDao.queryAllGroup(conn);
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	public void queryMothStillSumByCondition(String timeStart, String timeEnd, PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " *, date_format(month, '%Y-%m') AS formatMonth ";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and date_format(month, '%Y-%m') >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and date_format(month, '%Y-%m') <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " t_yb_myzk ", resultFeilds, " ",
					condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	public void queryUserFeeByCondition(String timeStart, String timeEnd, String userName, String realName, PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " * ";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(timeStart)) {
			condition.append(" and rq >= '"
					+ StringEscapeUtils.escapeSql(timeStart) + "'");
		}
		if (StringUtils.isNotBlank(timeEnd)) {
			condition.append(" and rq <= '"
					+ StringEscapeUtils.escapeSql(timeEnd) + "'");
		}
		if (StringUtils.isNotBlank(userName)) {
			condition.append(" and username like '%"
					+ StringEscapeUtils.escapeSql(userName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(realName)) {
			condition.append(" and realName like '%"
					+ StringEscapeUtils.escapeSql(realName.trim()) + "%'");
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_user_fee", resultFeilds, " ",
					condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
	public void queryInvestorDqzkByCondition(String userName, String realName, int groupId, double investSumStart, double investSumEnd, double dqzkStart, double dqzkEnd, PageBean<Map<String, Object>> pageBean) throws Exception {
		String resultFeilds = " * ";
		StringBuffer condition = new StringBuffer();
		if (StringUtils.isNotBlank(userName)) {
			condition.append(" and username like '%"
					+ StringEscapeUtils.escapeSql(userName.trim()) + "%'");
		}
		if (StringUtils.isNotBlank(realName)) {
			condition.append(" and realName like '%"
					+ StringEscapeUtils.escapeSql(realName.trim()) + "%'");
		}
		if (groupId > 0){
			condition.append(" and reffereeId in ( select userId from t_group_user where groupId = '"+groupId+"')");
		}
		if (investSumStart > 0){
			condition.append(" and investSum >= "+investSumStart);
		}
		if (investSumEnd > 0){
			condition.append(" and investSum <= "+investSumEnd);
		}
		if (dqzkStart > 0){
			condition.append(" and dqzk >= "+dqzkStart);
		}
		if (dqzkEnd > 0){
			condition.append(" and dqzk <= "+dqzkEnd);
		}
		Connection conn = MySQL.getConnection();
		try {
			dataPage(conn, pageBean, " v_t_zk_time", resultFeilds, " ",
					condition.toString());
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw e;
		} finally {
			conn.close();
		}
	}
}
